USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_SUMA_CAPITAL]    Script Date: 11/28/2011 11:59:11 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[WASP_M0005_SUMA_CAPITAL]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[WASP_M0005_SUMA_CAPITAL]
GO

USE [BMIG_Mensual_POST_CADENA]
GO

/****** Object:  StoredProcedure [dbo].[WASP_M0005_SUMA_CAPITAL]    Script Date: 11/28/2011 11:59:11 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROCEDURE [dbo].[WASP_M0005_SUMA_CAPITAL]
AS

BEGIN
		SELECT 
			SCCTA, 
			SCOPER, 
			SCSUC,
			SCRUB, 
			J50PCCBNT, 
			J50PCCPS, 
			SUM (SCSDO) capital,
			'N' as Securitizado,
			NULL AS sec01ptcta
		into	dbo.capital 
		FROM 
			Fsd011 A (NOLOCK), 
			J50PCC B (NOLOCK)
		WHERE 
			------- CAPITAL
			a.scmod in (30,32,46)and
			((SCRUB between '1116001000' AND '1116005000') OR	
			(SCRUB BETWEEN '1310001000' AND '1310005000')  OR
			(SCRUB between '1130001000' AND '1130004000')  OR
			(SCRUB BETWEEN '1330001000' AND '1330005000')  OR
			(SCRUB between '1140001000' AND '1140009000')  OR
			(SCRUB BETWEEN '1340001000' AND '1340005000')  OR
			(SCRUB BETWEEN '1340011000' AND '1340015000')  OR
			(SCRUB between '1150001000' AND '1150009000')  OR
			(SCRUB BETWEEN '1350001000' AND '1350005000')  OR
			(SCRUB BETWEEN '1350011000' AND '1350015000'))  AND 
			SCRUB=J50PCCBNT AND SCSDO<>0 
		GROUP BY SCCTA, SCOPER,	SCSUC,SCRUB,J50PCCPS, J50PCCBNT
		Union
		SELECT 
			SCCTA, 
			SCOPER,
			SCSUC,
			SCRUB, 
			J50PCCBNT, 
			J50PCCPS, 
			SUM (SCSDO) capital,
			'S' as Securitizado,
			sec01ptcta

		FROM 
			Fsd011 A (NOLOCK), 
			FSEC10 B (NOLOCK), 
			J50PCC C (NOLOCK) 
		WHERE
		-- Capital
			a.scmod in (30,32,46)and
			(  (SCRUB between '8816001000' AND '8830000000')  OR
			(SCRUB BETWEEN '8830001000' AND '8840000000')  OR
			(SCRUB BETWEEN '8840001000' AND '8840010000')  OR
			(SCRUB between '8850001000' AND '8860000000')  OR
			(scrub between 8910000000 and 8910004999) or
			(scrub between 8930000000 and 8930004999) or 
			(scrub between 8940000000 and 8940014999) or
			(scrub between 8950000000 and 8950014999)) AND
			A.SCCTA = B.Sec10AoCta AND
			A.SCOPER = B.Sec10AoOpe AND
			A.SCSUC = B.Sec10AoSuc AND
			--B.sec10stspt = 9 AND
			A.SCRUB=C.J50PCCBNT AND 
			B.sec01ptcta = C.J50PCCOPE AND
			A.SCSDO<>0 	
		GROUP BY SCCTA, SCOPER, Scsuc,SCRUB,J50PCCPS, J50PCCBNT,sec01ptcta
END
GO

